<?php if ( ! defined('BASEPATH')) exit('No direct script access allowed');

class Pencabang_model extends CI_Model{
	function __construct() {
		parent::__construct();
	}
	function get_all_cabang(){
		$this->db->select('*');
		$this->db->from('TbMDCabang');
		//$this->db->join('TblCOACabang','TbMDCabang.Nama=TblCOACabang.nama_cabang');
		$res_query = $this->db->get()->result_array();
		$nama = array('Semua'=>'Semua');
		foreach($res_query as $value){
			$nama['nama_cabang'][$value['Nama']]=$value['Nama'];
			//$nama['kode_cabang'][$value['Nama']]=$value['kode_coa'];
			$nama['kode_cabang'][$value['Nama']]=$value['Kode'];
			$nama['alamat_cabang'][$value['Nama']]=$value['Alamat'];
			$nama['telp_cabang'][$value['Nama']]=$value['Telp'];
		}

		return $nama;
	}
        
        function get_all_cabang_tanpa_semua(){
		$this->db->select('*');
		$this->db->from('TbMDCabang');
		//$this->db->join('TblCOACabang','TbMDCabang.Nama=TblCOACabang.nama_cabang');
		$res_query = $this->db->get()->result_array();
		foreach($res_query as $value){
			$nama['nama_cabang'][$value['Nama']]=$value['Nama'];
			//$nama['kode_cabang'][$value['Nama']]=$value['kode_coa'];
			$nama['kode_cabang'][$value['Nama']]=$value['Kode'];
			$nama['alamat_cabang'][$value['Nama']]=$value['Alamat'];
			$nama['telp_cabang'][$value['Nama']]=$value['Telp'];
		}

		return $nama;
	}
        
	function get_data_table($tgl_awal,$tgl_akhir){
		if($tgl_awal==''){
			$tgl_awal=date('Ymd');
		}else{
			$tgl_awal=$this->date_func->change_format_date($tgl_awal,'d-m-Y','Ymd');
		}
		if($tgl_akhir==''){
			$tgl_akhir=date('Ymd');
		}else{
			$tgl_akhir=$this->date_func->change_format_date($tgl_akhir,'d-m-Y','Ymd');
		}
		$string_query = '
			SELECT
			cabang_keberangkatan,
			SUM(total_penumpang) as total_penumpang,
			SUM(tiket_void) as tiket_void,
			SUM(total_omset) as total_omset,
			SUM(total_discount) as total_discount,
			SUM(fee_agen) as fee_agen,
			SUM(total_void) as total_void,
			SUM(total_setoran) as total_setoran,
			SUM(total_laba) as total_laba
			FROM TblSetoran
			WHERE tanggal BETWEEN "'.$tgl_awal.'" AND "'.$tgl_akhir.'"
			GROUP BY cabang_keberangkatan	
		';
		
		return $this->db->query($string_query)->result_array();
	}
	
	function get_data_table_cabang($tgl_awal,$tgl_akhir,$cabang){
			if($tgl_awal==''){
				$tgl_awal=date('Ymd');
			}else{
				$tgl_awal=$this->date_func->change_format_date($tgl_awal,'d-m-Y','Ymd');
			}
			if($tgl_akhir==''){
				$tgl_akhir=date('Ymd');
			}else{
				$tgl_akhir=$this->date_func->change_format_date($tgl_akhir,'d-m-Y','Ymd');
			}
			$string_query = '
				SELECT
				cabang_keberangkatan,
				SUM(total_penumpang) as total_penumpang,
				SUM(tiket_void) as tiket_void,
				SUM(total_omset) as total_omset,
				SUM(total_discount) as total_discount,
				SUM(fee_agen) as fee_agen,
				SUM(total_void) as total_void,
				SUM(total_setoran) as total_setoran,
				SUM(total_laba) as total_laba
				FROM TblSetoran
				WHERE tanggal BETWEEN "'.$tgl_awal.'" AND "'.$tgl_akhir.'" AND cabang_keberangkatan LIKE "%'.$cabang.'%"
				GROUP BY cabang_keberangkatan
			';
			return $this->db->query($string_query)->result_array();
	}
        
        function get_grafik_hari_cabang_tujuan($cabang,$bulan,$tahun)
        {
            $this->db->select('DAY(tanggal) AS tanggal, SUM(total_omset) AS total_omset, SUM(total_setoran) as total_setoran, SUM(total_laba) as total_laba, SUM(jumlah) as jumlah');
            $this->db->join('TblBiaya','TblBiaya.NoSPJ=TblSetoran.NoSPJ','inner');
            $this->db->where('cabang_keberangkatan',$cabang);
            $this->db->where('MONTH(tanggal)',$bulan);
            $this->db->where('YEAR(tanggal)',$tahun);
            $this->db->where('is_tujuan',1);
            $this->db->group_by('DAY(tanggal)');
            return $this->db->get('TblSetoran')->result_array();
        }
        
        function get_month()
        {
            return array("January","February","March","April","May","June","July","August","September","October","November","December");
        }
        
        function get_year()
        {
            for($i=0;$i<20;$i++)
            {
                $array[(date('Y')-$i)]=(date('Y')-$i);
            }
            return $array;
        }
        
        function get_grafik_hari_cabang_asal($cabang,$bulan,$tahun)
                {
            $this->db->select('SUM(jumlah) as jumlah');
            $this->db->join('TblBiaya','TblBiaya.NoSPJ=TblSetoran.NoSPJ','inner');
            $this->db->where('cabang_keberangkatan',$cabang);
            $this->db->where('MONTH(tanggal)',$bulan);
            $this->db->where('YEAR(tanggal)',$tahun);
            $this->db->where('is_tujuan',0);
            $this->db->group_by('DAY(tanggal)');
            return $this->db->get('TblSetoran')->result_array();
        }
        
        function get_grafik_bulan_cabang_tujuan($cabang,$tahun)
        {
            $this->db->select('MONTH(tanggal) AS tanggal, SUM(total_omset) AS total_omset, SUM(total_setoran) as total_setoran, SUM(total_laba) as total_laba, SUM(jumlah) as jumlah');
            $this->db->join('TblBiaya','TblBiaya.NoSPJ=TblSetoran.NoSPJ','inner');
            $this->db->where('cabang_keberangkatan',$cabang);
            $this->db->where('YEAR(tanggal)',$tahun);
            $this->db->where('is_tujuan',1);
            $this->db->group_by('MONTH(tanggal)');
            return $this->db->get('TblSetoran')->result_array();
        }
        
        function get_grafik_bulan_cabang_asal($cabang,$tahun)
        {
            $this->db->select('MONTH(tanggal) AS tanggal, SUM(total_omset) AS total_omset, SUM(total_setoran) as total_setoran, SUM(total_laba) as total_laba, SUM(jumlah) as jumlah');
            $this->db->join('TblBiaya','TblBiaya.NoSPJ=TblSetoran.NoSPJ','inner');
            $this->db->where('cabang_keberangkatan',$cabang);
            $this->db->where('YEAR(tanggal)',$tahun);
            $this->db->where('is_tujuan',0);
            $this->db->group_by('MONTH(tanggal)');
            return $this->db->get('TblSetoran')->result_array();
        }
        
        function get_grafik_hari_produk_cabang($cabang,$bulan,$tahun)
        {
            $this->db->select('DAY(tanggal) AS tanggal, SUM(total_penumpang) AS total_penumpang, SUM(total_kapasitas) as total_kapasitas');
            $this->db->where('cabang_keberangkatan',$cabang);
            $this->db->where('MONTH(tanggal)',$bulan);
            $this->db->where('YEAR(tanggal)',$tahun);
            $this->db->group_by('DAY(tanggal)');
            return $this->db->get('TblSetoran')->result_array();
        }
        
        function get_grafik_bulan_produk_cabang($cabang,$tahun)
        {
            $this->db->select('MONTH(tanggal) AS tanggal, SUM(total_penumpang) AS total_penumpang, SUM(total_kapasitas) as total_kapasitas');
            $this->db->where('cabang_keberangkatan',$cabang);
            $this->db->where('YEAR(tanggal)',$tahun);
            $this->db->group_by('MONTH(tanggal)');
            return $this->db->get('TblSetoran')->result_array();
        }


        function get_biaya_langsung($tgl_awal,$tgl_akhir){
		if($tgl_awal==''){
			$tgl_awal=date('Ymd');
		}else{
			$tgl_awal=$this->date_func->change_format_date($tgl_awal,'d-m-Y','Ymd');
		}
		if($tgl_akhir==''){
			$tgl_akhir=date('Ymd');
		}else{
			$tgl_akhir=$this->date_func->change_format_date($tgl_akhir,'d-m-Y','Ymd');
		}
		$string_query = '
			SELECT
			TblSetoran.cabang_keberangkatan,
			SUM(jumlah) as biaya_langsung
			FROM TblBiaya
			INNER JOIN TblSetoran
			ON TblBiaya.NoSPJ=TblSetoran.NoSPJ
			WHERE is_tujuan=1 AND TblSetoran.tanggal BETWEEN "'.$tgl_awal.'" AND "'.$tgl_akhir.'"
			GROUP BY TblSetoran.cabang_keberangkatan	
		';
		$data = $this->db->query($string_query)->result_array();
		if(count($data)>0){
			for($i=0;$i<count($data);$i++){
				$retval[$data[$i]['cabang_keberangkatan']]=$data[$i]['biaya_langsung'];
			}
		}else{
			$retval = FALSE;
		}
		return $retval;
	}
	function get_biaya_langsung_cabang($tgl_awal,$tgl_akhir,$cabang){
			if($tgl_awal==''){
				$tgl_awal=date('Ymd');
			}else{
				$tgl_awal=$this->date_func->change_format_date($tgl_awal,'d-m-Y','Ymd');
			}
			if($tgl_akhir==''){
				$tgl_akhir=date('Ymd');
			}else{
				$tgl_akhir=$this->date_func->change_format_date($tgl_akhir,'d-m-Y','Ymd');
			}
			$string_query = '
				SELECT
				TblSetoran.cabang_keberangkatan,
				SUM(jumlah) as biaya_langsung
				FROM TblBiaya
				INNER JOIN TblSetoran
				ON TblBiaya.NoSPJ=TblSetoran.NoSPJ
				WHERE is_tujuan=1 AND TblSetoran.tanggal BETWEEN "'.$tgl_awal.'" AND "'.$tgl_akhir.'" AND TblSetoran.cabang_keberangkatan LIKE "%'.$cabang.'%"
				GROUP BY TblSetoran.cabang_keberangkatan	
			';
			$data = $this->db->query($string_query)->result_array();
			if(count($data)>0){
				for($i=0;$i<count($data);$i++){
					$retval[$data[$i]['cabang_keberangkatan']]=$data[$i]['biaya_langsung'];
				}
			}else{
				$retval = FALSE;
			}
			return $retval;
	}
	function get_biaya_opawal($tgl_awal,$tgl_akhir){
		if($tgl_awal==''){
			$tgl_awal=date('Ymd');
		}else{
			$tgl_awal=$this->date_func->change_format_date($tgl_awal,'d-m-Y','Ymd');
		}
		if($tgl_akhir==''){
			$tgl_akhir=date('Ymd');
		}else{
			$tgl_akhir=$this->date_func->change_format_date($tgl_akhir,'d-m-Y','Ymd');
		}
		$string_query = '
			SELECT
			TblSetoran.cabang_keberangkatan,
			SUM(jumlah) as biaya_opawal
			FROM TblBiaya
			INNER JOIN TblSetoran
			ON TblBiaya.NoSPJ=TblSetoran.NoSPJ
			WHERE is_tujuan=0 AND TblSetoran.tanggal BETWEEN "'.$tgl_awal.'" AND "'.$tgl_akhir.'"
			GROUP BY TblSetoran.cabang_keberangkatan	
		';
		$data = $this->db->query($string_query)->result_array();
		if(count($data)>0){
			for($i=0;$i<count($data);$i++){
				$retval[$data[$i]['cabang_keberangkatan']]=$data[$i]['biaya_opawal'];
			}
		}else{
			$retval=FALSE;
		}
		return $retval;
	}
	function get_biaya_opawal_cabang($tgl_awal,$tgl_akhir,$cabang){
			if($tgl_awal==''){
				$tgl_awal=date('Ymd');
			}else{
				$tgl_awal=$this->date_func->change_format_date($tgl_awal,'d-m-Y','Ymd');
			}
			if($tgl_akhir==''){
				$tgl_akhir=date('Ymd');
			}else{
				$tgl_akhir=$this->date_func->change_format_date($tgl_akhir,'d-m-Y','Ymd');
			}
			$string_query = '
				SELECT
				TblSetoran.cabang_keberangkatan,
				SUM(jumlah) as biaya_opawal
				FROM TblBiaya
				INNER JOIN TblSetoran
				ON TblBiaya.NoSPJ=TblSetoran.NoSPJ
				WHERE is_tujuan=0 AND TblSetoran.tanggal BETWEEN "'.$tgl_awal.'" AND "'.$tgl_akhir.'" AND TblSetoran.cabang_keberangkatan="%'.$cabang.'%"
				GROUP BY TblSetoran.cabang_keberangkatan	
			';
			$data = $this->db->query($string_query)->result_array();
			if(count($data)>0){
				for($i=0;$i<count($data);$i++){
					$retval[$data[$i]['cabang_keberangkatan']]=$data[$i]['biaya_opawal'];
				}
			}else{
				$retval=FALSE;
			}
			return $retval;
	}
}